package com.dxw.utils;

import com.dxw.entity.Student;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class ExcelUtils {

    //导出excel的文件
    public static InputStream exportExcelXlsx(List<Student> students){
        //创建一个workbook的工作簿
        Workbook wb = new XSSFWorkbook();
        //创建一个sheet
        Sheet sheet = wb.createSheet("学生信息");
        //创建row(行)第一行
        String[] headers = {"编号","姓名" ,"密码",  "性别", "年龄","专业"};
        Row row1 = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            //创建第一行所有列的信息
            Cell cell = row1.createCell(i);
            cell.setCellValue(headers[i]);
        }
        //创建其他行
        for (int i = 0; i < students.size(); i++) {
            //从第二行开始
            Row row = sheet.createRow(i + 1);
            //得到学生的信息
            Student student = students.get(i);
            row.createCell(0).setCellValue(student.getSid());
            row.createCell(1).setCellValue(student.getSpassword());
            row.createCell(2).setCellValue(student.getName());
            row.createCell(3).setCellValue(student.getSex());
            row.createCell(4).setCellValue(student.getAge());
            row.createCell(5).setCellValue(student.getMajor());
        }
        //自适应宽高
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }
        //保存在本地
        String path = "D:\\gc1223_1_excel\\学生信息.xlsx";
        try {
            wb.write(new FileOutputStream(path));
            //得到我们的保存路径
            return new FileInputStream(path);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    //读取excel文件
    public static Object[][] importExcelXlsx(InputStream in){
        try {
            //得到excel的文件
            Workbook wb = new XSSFWorkbook(in);
            //获取第一个表
            Sheet sheet = wb.getSheetAt(0);
            //获取行数
            int rowsNums = sheet.getPhysicalNumberOfRows();
            System.out.println("行数："+rowsNums);
            //创建参数的二维数组
            Object[][] params = new Object[rowsNums-1][6];
            DataFormatter df = new DataFormatter();
            //从第二行开始
            for (int i = 1; i < rowsNums; i++) {
               //拿到每一行(从第二行开始)
                Row row = sheet.getRow(i);
                params[i-1][0]=df.formatCellValue(row.getCell(0));
                params[i-1][1]=df.formatCellValue(row.getCell(1));
                params[i-1][2]=df.formatCellValue(row.getCell(2));
                params[i-1][3]=df.formatCellValue(row.getCell(3));
                params[i-1][4]=Integer.parseInt(df.formatCellValue(row.getCell(4)));
                params[i-1][5]=df.formatCellValue(row.getCell(5));
            }
            return params;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

}
